Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# FILEVIEW

The FILEVIEW command works in conjunction with the FILENEXT function to read one record at a time of an input file, process the data, and store the data in Oracle OLAP dimensions and variables according to the descriptions of the fields. Use FILENEXT to read the record, then use one or more FILEVIEW statements to process the fields as needed. FILEVIEW has the same attributes as FILEREAD for specifying the format of the input and the processing of the output.

Syntax

FILEVIEW fileunit [field-desc...]

Arguments

fileunit

  A fileunit number that is assigned to a file opened for reading (READ mode) in a previous call to the FILEOPEN function.

field-desc

  A field description describes how to process one or more fields in each input record. Attributes in the field description specify how to format the input data. FILEVIEW reads each field according to the format specification and assigns the input data to the specified object. You can assign the data to the object directly or you can specify an expression to manipulate the data before you assign it. One field description can assign data from one input field to one Oracle OLAP object. Alternately you can use the ACROSS keyword to assign several values in the input record to a variable that is dimensioned by the fastest varying dimension. Because field attributes include the column number in the input record, you can process input fields in any order.

  The format for the field description is as follows.

  [[pos] ACROSS dim [limit-clause]:] [attribs] object [= exp]

pos

  One or more attributes that specify the position in the record where Oracle OLAP will begin reading the fields specified by the ACROSS description. To specify the position, use the attributes FIELD, SPACE, and COLUMN (see FILEREAD). The pos argument is optional when the series of fields specified in the ACROSS phrase begins in the next field for structured records, or the next byte for ruled records.

ACROSS dim [limit-clause]:

  Specifies the dimension of one or more data fields in the input record. FILEVIEW assigns the data in the fields to a variable according to the values in the current status of dim. Typically, each field description processes one value. However, using the ACROSS keyword, you can process one input value for each dimension value currently in the status.

  limit-clause lets you temporarily change the status of the fastest varying dimension, as long as you are not in a FOR loop over the that dimension. The new status is in effect only for the duration of the FILEVIEW command. The format of limit-clause is as follows.

  [ADD|COMPLEMENT|KEEP|REMOVE|TO|INSERT] valuelist

  To specify the temporary status, insert any of the LIMIT keywords (the default is TO) along with an appropriate list of dimension values or related dimensions. You can use any valid LIMIT clause (see the LIMIT command for further information). The following example limits month to the last six values, no matter what the current status of month is.

      across month last 6: units

attribs

  One or more attributes that tell Oracle OLAP the position in the record and the format of the input data. (See FILEREAD for an explanation of the available attributes.)

object [= exp]

  An Oracle OLAP variable, dimension, or relation to which the input data is assigned. When = exp is missing, the data is assigned implicitly to the object. When = exp is present, the data is processed according to the expression and then assigned to object.

  You can use the keyword VALUE to represent the value in a particular field of a record. VALUE represents the data from the file, formatted according to the FILEREAD attributes you use. When the field in the record is blank, FILEREAD considers its value to be `NA`. By default, the data type of VALUE is the data type of the target object. However, you can specify a different data type with an attribute keyword. VALUE can be used more than once to represent different values from the same record. For each instance, specify the column from which to read each value, as shown in the following example code.

      sales = if col 1 w 1 text value eq 'A' then col 2 w 8 value -
         else col 10 w 8 value

  In this example, the default data type of VALUE is decimal, which is the data type of the target object `sales`. However, the first instance of VALUE is compared to a text expression, so you must use the attribute TEXT to specify its data type.

SELECT exp

  The SELECT field-description keyword processes varying record types (such as records with different structures or different target objects) with one FILEVIEW statement. Within a field description, you can use the following syntax:

  SELECT exp -

  [WHEN exp action [WHEN exp action ...]] -

  [ELSE action]

  IF bool-exp THEN action [ELSE action]

  DO

  field-desc

  [field-desc]

  ...

  DOEND

  The action argument is one of the following:

  - NULL (no action occurs)

  - field-description, including nested IF and SELECT statements.

  SELECT evaluates the first expression, which may contain invocations of the VALUE keyword, and which has a default data type of TEXT. SELECT then sequentially compares the result with the WHEN expressions. When the first match is found, the associated action occurs. When no match is found, the ELSE action (if specified) occurs.

IF bool-exp

  The IF field-description keyword processes varying record types (such as records with different structures or different target objects) with one FILEVIEW statement. Within a field description, you can use the following syntax.

  IF bool-exp THEN action [ELSE action]

  action is the same as described for SELECT.

  IF evaluates the Boolean expression, which may contain invocations of the VALUE keyword. IF performs the THEN action when the expression is TRUE or the ELSE action, if specified, when the expression is FALSE. No action occurs when the expression is `NA`.

Notes

Record Order

FILEVIEW can process the fields in a record in any order. List the field descriptions in the order you want to process them, identifying the fields with explicit column numbers. You can also use several FILEVIEW statements on the same record to do different processing depending on the data you find in the record.

Alternative OLAP DML Statement

When you want to process all the records in a file in the same way, without complicated optional processing, a FILEREAD statement is easier to use.

Dimension Values

When the target object of a field description is a dimension, you can specify whether the data in the file will be used to add values to the dimension or not. The dimension attributes are MATCH and APPEND:

- MATCH -- Any value encountered in a field must already be a value of the dimension. FILEVIEW temporarily limits status to that value. When it is not already a dimension value, FILEVIEW generates an error. After executing a FILEVIEW statement, the dimension status is the same as before the execution of the statement.

- APPEND -- The values in the field can already exist or they can be new. When the value exists, FILEVIEW limits status to that value; when it does not, FILEVIEW adds the value and then limits status. The dimension is limited to ALL when FILEVIEW is finished.

For more information about handling dimensions, see FILEREAD.

Handling Errors When FILEVIEW Encounters an Error

When FILEVIEW encounters an error, you can control what happens with an error trap and appropriate processing. Errors can be caused by attempts to convert data to an incompatible data type or by encountering invalid dimension values. You can use the FILEERROR function to find out what type of error occurred. After processing the error, you can use GOTO to branch back to the FILEVIEW statement.

Attribute List

For a complete list of the attributes for FILEVIEW and FILEREAD and for more information about processing `NA` values, reading date values, reading multidimensional data, storing NTEXT values, and specifying attributes, see FILEREAD.

FILEVIEW with Composites

The discussions of composites and variables dimensioned by composites in FILEREAD also apply to FILEVIEW.

Examples

Example 13-15 Varying Months

The following program processes an input file that contains sales data for a variable number of months. The file has the following records:

- Record 1 -- Title (to be ignored).

- Record 2 -- Column labels. Month names are used to set the status of `month`. The number of months is unknown before processing the file.

- Record 3 -- Dashes underlining column labels (to be ignored).

- Record 4 -- Blank.

- Record 5 to end -- There are three record types for Record 5—one for each type of line to be read.

One record type for Record 5 represents a detail line with the contents shown in the following table.

Column | Width | Format | Data
--- | --- | --- | ---
1 | 8 | Symbolic | District name or blank (When the district name is blank on a detail line, the most recent line containing a district determines the current district.)
10 | 10 | Symbolic | Product name
21 | 10 | Symbolic | Sales for first month
33 | 10 | Symbolic | Sales for second month
45 | To end of record | Symbolic | Sales for additional months

Another record type in Record 5 represents a totals line with the contents shown in the following table.

Column | Width | Data
--- | --- | ---
1 | 18 | Blank
21 | To end of record | Totals

A third record type of Record 5 contains dashes or equal signs as row separators as illustrated in the following table.

Column | Width | Data
--- | --- | ---
1 | 18 | Blank
21 | To end of record | Dashes (--) or equal signs (==)

This is a report of the sample file.

    This is the Title
                        Jan95      Feb95      Mar95      Apr95
                        ---------- ---------- ---------- ----------

    Boston   Tents       32,153.52  32,536.30  43,062.75  57,608.39
             Canoes      66,013.92  76,083.84  91,748.16 125,594.28
             Racquets    52,420.86  56,837.88  58,838.04  69,338.88
             Sportswear  53,194.70  58,913.40  62,797.80  67,869.10
             Footwear    91,406.82  86,827.32 100,199.46 107,526.66
                        ---------- ---------- ---------- ----------
                        295,189.82 311,198.74 356,646.21 427,937.31
                        ---------- ---------- ---------- ----------
    Atlanta  Tents       40,674.20  44,236.55  51,227.06  78,469.37
               .
               .
               .
             Footwear    53,284.54  57,331.30  59,144.76  70,516.98
                        ---------- ---------- ---------- ----------
                        231,780.46 245,812.33 275,622.68 355,784.92
                        ---------- ---------- ---------- ----------
                         1,813,326  1,985,731  2,185,174  2,638,409
                        ========== ========== ========== ==========

The program figures out which months are covered in the file, then reads the detail lines and assigns the sales data to the appropriate district and month. The program ignores total lines and underlines when FILEVIEW finds columns 1 through 19 blank. The program takes the name of the data file as an argument.

    DEFINE salesdata PROGRAM
    LD Store Several Months of Sales Data in an Analytic Workspace
    PROGRAM
    VARIABLE fil.unit INTEGER
    VARIABLE flag BOOLEAN
    VARIABLE mname TEXT
    VARIABLE label TEXT
    VARIABLE savedist TEXT

    TRAP ON error NOPRINT
    PUSH month district
    fil.unit = FILEOPEN(ARG(1) READ)

    IF FILENEXT(fil.unit) NE YES    "Skip Record 1
      THEN SIGNAL noread
    IF FILENEXT(fil.unit) NE YES    "Process Record 2
      THEN SIGNAL noread
    FILEVIEW fil.unit COLUMN 21 ACROSS month: -
      WIDTH 10 mname = JOINLINES( mname VALUE)
    LIMIT month TO mname
    IF FILENEXT(fil.unit) NE YES     "Skip Record 3
      THEN SIGNAL noread
    IF FILENEXT(fil.unit) NE YES     "Skip Record 4
      THEN SIGNAL noread

    WHILE FILENEXT(fil.unit)  "Process Record 5 To End Of File
       DO
       "Store Value In Local Label Variable
       FILEVIEW fil.unit COLUMN 1 WIDTH 18 label
       IF label NE NA         "Check For NA (Blank Field)
         THEN DO              "Get District Value If Present
          IF EXTCHARS(label, 1, 8) NE '      '
             "Set District Status
             THEN savedist = BLANKSTRIP(EXTCHARS(label, 1, 8))
           FILEVIEW fil.unit -
             COLUMN 1 WIDTH 8 district = IF VALUE NE NA THEN -
                VALUE ELSE savedist -
             COLUMN 10 WIDTH 10 product -
             COLUMN 19 ACROSS month: WIDTH 10 SPACE 2 -
                SCALE 2 newsales
          DOEND
    NEXT:
      DOEND

    FILECLOSE fil.unit
    POP month district
    RETURN
    error:
    IF fil.unit EQ NA
      THEN SHOW JOINCHARS('Can\'t Open Data File ' ARG(1) '.')
    ELSE IF ERRORNAME NE 'attn' AND ERRORNAME NE 'QUIT'
      THEN DO
        SHOW JOINCHARS('RECORD ' RECNO(fil.unit) ' is invalid.')
        GOTO NEXT
      DOEND
    ELSE IF ERRORNAME EQ 'noread'
      THEN DO
        SHOW 'File Too Short.'
        FILECLOSE fil.unit
      DOEND
    ELSE DO
      SHOW 'Data Import Interrupted.'
      FILECLOSE fil.unit
    DOEND
    POP month district
    RETURN

Example 13-16 Additional Processing

When you want to save the dimension value that FILEVIEW read for display or further processing, you can read the field again and save the value in a variable. These lines in a program display the name of the month that FILEVIEW read. The FILEVIEW command saves the month value in column 1 in a variable called `mname`.

    WHILE FILENEXT(fil.unit)
    DO
      FILEVIEW fil.unit WIDTH 8 month WIDTH 5 INTEGER units -
           COLUMN 1 WIDTH 8 mname
      SHOW mname PROMPT
    DOEND

Example 13-17 Using the VALUE Keyword as a Function

Suppose you want to read and report data from a disk file similar to the following, named `numbers.dat`, which has columns 15 characters wide.

    1.0            2.0            3.0            4.0            5.0
              -1.0           -2.0           -3.0           -4.0           -5.0
               0.0            0.0            1.43900000E+03                1.39900000E+03

You can read this data using the VALUE keyword as a function with FILEVIEW in a program similar to the following one (named `try`). However, this first example does not work. The FILEVIEW command will skip fields. The reason for the data skipping is that each time FILEREAD fetches a field from the current record, it updates the column pointer to point past the field. When the next fetch does not specify a position (using the COLUMN, SPACE, or FIELD attribute), data will be read from the default position established by the previous fetch. This usually desirable behavior will not work when more than one fetch is needed to perform a single assignment. This happens when the VALUE function is coded twice in the same IF...THEN...ELSE block, as shown here. The NAMELIST and DIRLIST attributes return one value for multiple versions of a particular file name in the directory. The NAMELIST attribute also returns only one value for multiple files in the directory with the same root file name but different file types.

    DEFINE try PROGRAM
    PROGRAM
    VARIABLE funit INTEGER
    DEFINE dvar VARIABLE DECIMAL <year>
    PUSH year
    LIMIT year TO LAST 5
    TRAP ON ERROR
    funit=FILEOPEN('numbers.dat' R)

    WHILE FILENEXT(funit)
       DO
       FILEVIEW funit ACROSS year: W 15 TEXT dvar = -
          IF FINDCHARS(VALUE, 'e') EQ 0 -  "Incorrect Use of Value
          THEN CONVERT(VALUE, dec) -       "Results in Skipped
          ELSE -9999.99                    "Fields
          REPORT DOWN year dvar
       DOEND
    error:
    FILECLOSE funit
    DELETE dvar
    POP year
    END

When you execute the `try` program,

    try

the output skips numbers, as in the following.

    YEAR              DVAR
    -------------  ----------
    Yr93                 2.00
    Yr94                 4.00
    Yr95                   NA
    Yr96            -9,999.99
    Yr97            -9,999.99

    YEAR              DVAR
    -------------  ----------
    Yr93                -2.00
    Yr94                -4.00
    Yr95                   NA
    Yr96            -9,999.99
    Yr97            -9,999.99

    YEAR              DVAR
    -------------  ----------
    Yr93                 0.00
    Yr94            -9,999.99
    Yr95            -9,999.99
    Yr96            -9,999.99
    Yr97            -9,999.99

However, when the SPACE attribute is used to make the second VALUE back up some distance so it reads the same field that the first VALUE read, everything works fine. SPACE can be used in the preceding sample program by changing the THEN clause to the following clause.

    THEN CONVERT(SPACE -15 VALUE, dec) -

Now when you execute the program,

    try

the output will look like this.

    YEAR              DVAR
    -------------  ----------
    Yr93                 1.00
    Yr94                 2.00
    Yr95                 3.00
    Yr96                 4.00
    Yr97                 5.00

    YEAR              DVAR
    -------------  ----------
    Yr93                -1.00
    Yr94                -2.00
    Yr95                -3.00
    Yr96                -4.00
    Yr97                -5.00

    YEAR              DVAR
    -------------  ----------
    Yr93                 0.00
    Yr94                 0.00
    Yr95            -9,999.99
    Yr96            -9,999.99
    Yr97            -9,999.99